CREATE TABLE [dbo].[SupplementMain]
(
[SupplementKey] [uniqueidentifier] NOT NULL,
[Name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_SupplementMain_Name] DEFAULT (''),
[Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_SupplementMain_Description] DEFAULT (''),
[DocumentVersionKey] [uniqueidentifier] NULL,
[SupplementStatusCode] [int] NOT NULL CONSTRAINT [DF_SupplementMain_SupplementStatusCode] DEFAULT ((0)),
[Cost] [decimal] (18, 4) NULL CONSTRAINT [DF_SupplementMain_Cost] DEFAULT ((0)),
[CostCollection] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UpdatedOn] [datetime] NOT NULL CONSTRAINT [DF_SupplementMain_UpdatedOn] DEFAULT (getdate()),
[UpdatedByUserKey] [uniqueidentifier] NOT NULL,
[CreatedByUserKey] [uniqueidentifier] NOT NULL,
[CreatedOn] [datetime] NOT NULL,
[AccessKey] [uniqueidentifier] NOT NULL,
[SupplementTypeKey] [uniqueidentifier] NOT NULL,
[SystemEntityKey] [uniqueidentifier] NOT NULL,
[NotificationGroupKey] [uniqueidentifier] NULL,
[MarkedForDeleteOn] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
CREATE TRIGGER [dbo].[asi_SupplementMain_Insert_Update]
    ON [dbo].[SupplementMain]
    FOR INSERT, UPDATE
AS
UPDATE    pm1
   SET    pm1.Cost = Coalesce(
        (SELECT    Sum(sm2.Cost)
           FROM    PackageMain pm2
                INNER JOIN PackageItem pi2 ON pm2.PackageKey = pi2.PackageKey
                INNER JOIN SupplementMain sm2 ON pi2.SupplementKey = sm2.SupplementKey
          WHERE    pm2.PackageKey = pm1.PackageKey), 0)
  FROM    PackageMain pm1
        INNER JOIN PackageItem pi1 ON pm1.PackageKey = pi1.PackageKey
        INNER JOIN inserted ON pi1.SupplementKey = inserted.SupplementKey

GO
ALTER TABLE [dbo].[SupplementMain] ADD CONSTRAINT [PK_SupplementMain] PRIMARY KEY CLUSTERED ([SupplementKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SupplementMain_AccessKey] ON [dbo].[SupplementMain] ([AccessKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SupplementMain_CreatedByUserKey] ON [dbo].[SupplementMain] ([CreatedByUserKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SupplementMain_NotificationGroupKey] ON [dbo].[SupplementMain] ([NotificationGroupKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SupplementMain_SupplementStatusCode] ON [dbo].[SupplementMain] ([SupplementStatusCode]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SupplementMain_SupplementTypeKey] ON [dbo].[SupplementMain] ([SupplementTypeKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SupplementMain_SystemEntityKey] ON [dbo].[SupplementMain] ([SystemEntityKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SupplementMain_UpdatedByUserKey] ON [dbo].[SupplementMain] ([UpdatedByUserKey]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SupplementMain] ADD CONSTRAINT [FK_SupplementMain_AccessMain] FOREIGN KEY ([AccessKey]) REFERENCES [dbo].[AccessMain] ([AccessKey])
GO
ALTER TABLE [dbo].[SupplementMain] ADD CONSTRAINT [FK_SupplementMain_GroupMain] FOREIGN KEY ([NotificationGroupKey]) REFERENCES [dbo].[GroupMain] ([GroupKey])
GO
ALTER TABLE [dbo].[SupplementMain] ADD CONSTRAINT [FK_SupplementMain_SupplementStatusRef] FOREIGN KEY ([SupplementStatusCode]) REFERENCES [dbo].[SupplementStatusRef] ([SupplementStatusCode])
GO
ALTER TABLE [dbo].[SupplementMain] ADD CONSTRAINT [FK_SupplementMain_SupplementTypeRef] FOREIGN KEY ([SupplementTypeKey]) REFERENCES [dbo].[SupplementTypeRef] ([SupplementTypeKey])
GO
ALTER TABLE [dbo].[SupplementMain] ADD CONSTRAINT [FK_SupplementMain_SystemEntity] FOREIGN KEY ([SystemEntityKey]) REFERENCES [dbo].[SystemEntity] ([SystemEntityKey])
GO
ALTER TABLE [dbo].[SupplementMain] ADD CONSTRAINT [FK_SupplementMain_UniformRegistry] FOREIGN KEY ([SupplementKey]) REFERENCES [dbo].[UniformRegistry] ([UniformKey])
GO
ALTER TABLE [dbo].[SupplementMain] ADD CONSTRAINT [FK_SupplementMain_UserMain_CreatedBy] FOREIGN KEY ([CreatedByUserKey]) REFERENCES [dbo].[UserMain] ([UserKey])
GO
ALTER TABLE [dbo].[SupplementMain] ADD CONSTRAINT [FK_SupplementMain_UserMain_UpdatedBy] FOREIGN KEY ([UpdatedByUserKey]) REFERENCES [dbo].[UserMain] ([UserKey])
GO